package com.wanghang89.gmail.util.db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.RowMapper;

import com.wanghang89.gmail.bean.Url;

public class UrlDbUtils extends BaseDbUtils {
	
	private final static String SQL_SELECT_ALL = "select id,href,remark,status,url_type_id from url";
	private final static String SQL_ADD_ALL = "insert into url(href,remark,status,url_type_id) values(?,?,?,?)";
	
	private final static Logger LOGGER = Logger.getLogger(UrlDbUtils.class);
	private final static RowMapper<Url> RM_URL_ALL = new RowMapper<Url>() {

		@Override
		public Url mapRow(ResultSet rs, int index) throws SQLException {
			Url url = new Url();
			url.setId(rs.getInt("id"));
			url.setHref(rs.getString("href"));
			url.setRemark(rs.getString("remark"));
			url.setStatus(rs.getInt("status"));
			url.setUrlTypeId(rs.getInt("url_type_id"));
			return url;
		}
		
	};
	
	public UrlDbUtils()
	{
		
	}
	
	public Url getById(Integer id)
	{
		String sql = SQL_SELECT_ALL + " where id=?" ;
		Object[] params = new Object[]{id};
		List<Url> urls = jdbcTemplate.query(sql, params ,RM_URL_ALL);
		LOGGER.debug("urls size:"+urls.size());
		if (urls!=null&&!urls.isEmpty()) {
			Url url = urls.get(0);
			urls.clear();
			urls = null ;
			return url;
		}
		return null;
	}
	
	public Integer add(Url url) throws DBException
	{
		Url ou = getByHref(url.getHref());
		if (ou!=null) {
			Integer id = ou.getId();
			ou = null;
			return id;
		}
		Object[] params = {url.getHref(),url.getRemark(),url.getStatus(),url.getUrlTypeId()};
		Long id = add(SQL_ADD_ALL, params);
		if (id>0) {
			return id.intValue();
		}
		return 0;
	}
	
	public Url getByHref(String href)
	{
		String sql = SQL_SELECT_ALL+" where href=?";
		Object[] params = {href};
		List<Url> list = jdbcTemplate.query(sql,params,RM_URL_ALL);
		sql = null;
		params = null;
		if (list!=null&&!list.isEmpty()) {
			return list.get(0);
		}
		return null;
	}
	
	public List<Url> getAllUrls()
	{
		String sql = SQL_SELECT_ALL;
		List<Url> list = jdbcTemplate.query(sql,RM_URL_ALL);
		if (list!=null) {
			LOGGER.debug("all url list size: "+list.size());
			return list;
		}else {
			return new ArrayList<Url>();
		}
 	}
	
	
 	
}
